package com.youme.tools.cg.db.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;

public final class JdbcHelper {
  JdbcHelper() {}

  /**
   * 关闭数据库连接
   * 
   * @param conn Connection
   * @param pstmt PreparedStatement
   * @param rs ResultSet
   * @param cstmt CallableStatement
   */
  public static void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs,
      CallableStatement cstmt) {
    if (conn != null) {
      try {
        conn.close();
      } catch (SQLException e) {
        System.out.println(String.format("关闭数据库连接失败：%s", e.getMessage()));
      }
    }
    if (pstmt != null) {
      try {
        pstmt.close();
      } catch (SQLException e) {
        System.out.println(String.format("关闭数据库连接失败：%s", e.getMessage()));
      }
    }
    if (cstmt != null) {
      try {
        cstmt.close();
      } catch (SQLException e) {
        System.out.println(String.format("关闭数据库连接失败：%s", e.getMessage()));
      }
    }
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        System.out.println(String.format("关闭数据库连接失败：%s", e.getMessage()));
      }
    }
  }

  /**
   * 执行非查询操作
   * 
   * @param conn Connection
   * @param sql sql语句
   * @param params 参数集合
   * @param closeConn 是否关闭数据库连接
   * @return 影响数据行数
   * @throws SQLException
   */
  public static int executeUpdate(Connection conn, String sql, List<String> params,
      boolean closeConn) throws SQLException {
    PreparedStatement pstmt = null;
    int result = 0;
    try {
      pstmt = conn.prepareStatement(sql);
      setPstmtParams(pstmt, params);
      result = pstmt.executeUpdate();
    } catch (SQLException e) {
      throw e;
    } finally {
      if (closeConn) {
        closeAll(conn, pstmt, null, null);
      }
    }
    return result;
  }

  /**
   * 执行查询操作
   *
   * @param conn Connection
   * @param sql sql语句
   * @param params 参数集合
   * @param closeConn 是否关闭数据库连接
   * @return javax.servlet.jsp.jstl.sql.Result
   * @throws SQLException
   */
  public static Result executeQuery(Connection conn, String sql, List<String> params,
      boolean closeConn) throws SQLException {
    Result result = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = conn.prepareStatement(sql);
      setPstmtParams(pstmt, params);
      rs = pstmt.executeQuery();
      if (null != rs) {
        result = ResultSupport.toResult(rs);
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      if (closeConn) {
        closeAll(conn, pstmt, rs, null);
      }
    }
    return result;
  }

  /**
   * 为PreparedStatement设置参数
   * 
   * @param pstmt PreparedStatement
   * @param params 参数集合
   * @throws SQLException
   */
  private static void setPstmtParams(PreparedStatement pstmt, List<String> params)
      throws SQLException {
    if (params != null && params.size() > 0) {
      for (int i = 0; i < params.size(); i++) {
        if (null != params.get(i)) {
          pstmt.setString(i + 1, params.get(i));
        } else {
          pstmt.setNull(i + 1, java.sql.Types.NULL);
        }
      }
    }
  }

}
